<?php
///////////////////////////////////////////////////////////////////////////////
//
// global_pgsql.inc - Database related classes and functions
//
///////////////////////////////////////////////////////////////////////////////
//
// This file contains classes, functions which deals with the database related
// activities (connecting, execution of query etc.,)
//
// Author(s) : M.Sivakumar,S.Gnanavel,R.Lakshmi Narayanan
// Last Modified : 27.05.2003
//
// Copyright (c) BSG Leatherlink
//
///////////////////////////////////////////////////////////////////////////////

class cDatabase
        {
         // declaration of variables starts here
        var $host="localhost";
        var $database_name="payirdb";
        var $database_user_name="leatherlink";
        var $database_user_password="llink123";
        var $db_conn;
        var $query;
        var $result;
	var $search_path;
        /*****************************************************************************************
         Function Name         :cDatabase
         No. of Arguments        :Nil
         Arguments                :Nil
         Action                        :Connects to the database or displays an error message
                                                 when fails to connect
         Returns                        : Nil
         Created By                : S.Gnanavel
         Created On                : 14 March 2003
         Modified By                : R.Lakshmi Narayanan
         Modified On                : 19 March 2003
        *****************************************************************************************/

        function cDatabase()
                {
		$user_name=$_SESSION["guser_name"]?$_SESSION["guser_name"]:"$this->database_user_name";
                $this->db_conn=pg_pconnect("host=$this->host dbname=$this->database_name user=$user_name");
                // if database connection fails display the errors message
                if (!$this->db_conn)
                        {
                        trigger_error("Database Failure, Please try later", E_USER_ERROR);
			exit;
                        }
		else
			{	
			$this->setSearchPath();
			}		
		}
        function setSearchPath()
                {
		$this->search_path=($this->search_path)?$this->search_path:$_SESSION["search_path"];
		$this->search_path=($this->search_path)?$this->search_path:'payir_schema';
                $this->query="SET search_path=".pg_escape_string($this->search_path);
    	        $this->runQuery();
                }


        /*****************************************************************************************
         Function Name         : runQuery
         No. of Arguments        : Nil
         Arguments                : Nil
         Action                        : Connects to tbe database using the user defined function
                                                 ConnectToDatabase and executes the  query in the
                                                database
         Returns                        : Nil
         Created By                : S.Gnanavel
         Created On                : 14 March 2003
         Modified By                : R.Lakshmi Narayanan
         Modified On                : 20 March 2003
        *****************************************************************************************/

        function runQuery()
                {
                $this->result = pg_query($this->db_conn, $this->query);
                //remove this part before production use
                if (!$this->result)
                        {
			echo "<div class=\"error\">  Query Failed </div>";
                        $error=" Query Failed"."\n";
  			$debugging=debug_backtrace();
			foreach ($debugging as $debug)
				{
				echo "File = ".$debug["file"]."<br />";
				echo "Line = ".$debug["line"]."<br />";
				echo "Function = ".$debug["function"]."<br />";
				$error.="File = ".$debug["file"]."\n "."Line = ".$debug["line"]."\n "."Function = ".$debug["function"]."\n ";
				}
			echo $this->query;
			$error.=$this->query;
			error_log($error, 1,"masivakumar@gmail.com");
                        exit;
                       }
                }

        /*****************************************************************************************
         Function Name         : returnObjects
         No. of Arguments        : Nil
         Arguments                : Nil
         Action                        : Executes the query using user defined function  runQuery
                                                 and records are fetched interms of objects and stored
                                                in an array and returns it
         Returns                        : Array of records as Objects
         Created By                : S.Gnanavel
         Created On                : 14 March 2003
         Modified By                : R.Lakshmi Narayanan
         Modified On                : 20 March 2003
        *****************************************************************************************/

        function returnObjects()
                {
                // check the number of rows.  If not zero fetch rows as objects
                if (pg_num_rows($this->result) !=0)
                        {
                        for ($i=0; $i < pg_num_rows($this->result); $i++)
                                {
                                $objects[]=pg_fetch_object($this->result,$i);
                                }
                        }
                return $objects;
                }

        /*****************************************************************************************
         Function Name         : returnArrays
         No. of Arguments        : Nil
         Arguments                : Nil
         Action                        : Executes the query using user defined function runQuery
                                                 and records are fetched interms of arrays and stored
                                                in an array and returns it
         Returns                        : Array of records as Arrays
         Created By                : S.Gnanavel
         Created On                : 14 March 2003
         Modified By                : R.Lakshmi Narayanan
         Modified On                : 20 March 2003
        *****************************************************************************************/

        function returnArrays()
                {
                // check the number of rows, if not zero fetch rows as arrays
                if (pg_num_rows($this->result) !=0)
                        {
                        $arrays=pg_fetch_all($this->result);
			foreach($arrays as $key => $values)
				{
				foreach($values as $key1 => $value)
					{
					$arr=str_split($value);
					
					if(!in_array("<",$arr) && !in_array("-",$arr))
						{	
						$arrays1[$key][$key1]=addslashes($value);
						}
					else
						{
						$arrays1[$key][$key1]=$value;
						}
					}
				}
				
                	}
                return $arrays1;
                }


        /*****************************************************************************************
        Function Name         :returnNumArrays
        No. of Arguments        :Nil
        Arguments                :Nil
        Action                        :executes the query using the user defined function runQuery
                                                and returns the result as numeric arrays
        Returns                        :numeric arrays of rows
        Created By                :S.Gnanavel
        Created On                :14 March 2003
        Modified By                :R.Lakshmi Narayanan
        Modified On                :20 March 2003
        *****************************************************************************************/

        function returnNumArrays()
                {
                if (pg_num_rows($this->result) !=0)
                        {
                        for ($i=0; $i < pg_num_rows($this->result); $i++)
                                {
                                $num_arrays[]=pg_fetch_array($this->result,$i,PGSQL_NUM);
                                }
                        }
                return $num_arrays;
                }

        function saveQuery()
                {
/*                $FILE_NAME="$_SERVER[DOCUMENT_ROOT]/../sqlfiles/SQL.txt";
                $fp=fopen($FILE_NAME,"a");
                fwrite($fp,"\n".$this->query);
                fclose($fp);
*/
                }
        /*****************************************************************************************
        Function Name         :getNewId
        No. of Arguments        :Nil
        Arguments                :Nil
        Action                        :takes the table name and column name as arguments and returns the maximum 
                                value of the column - to be used in case of auto incremented columns
        Returns                        :maximum integer
        Created By                :Ma SivaKumar
        Created On                :10 August 2004
        *****************************************************************************************/

        function getNewId($table_name,$column_name)
                {
                $this->query="select $column_name from $table_name order by $column_name desc limit 1;";
                $this->runQuery();
                $id_array=$this->returnArrays();
                $Id=$id_array[0]["$column_name"];
                return $Id;
                }
        /*****************************************************************************************
        Function Name         :getConditionalColumn
        No. of Arguments        :Nil
        Arguments                :Nil
        Action                        :takes the table name and column name and condition arrays as arguments and                         returns the value of the column - to be used to get value of column matching a single 
                                condition
        Returns                        :column value
        Created By                :Ma SivaKumar
        Created On                :10 August 2004
        *****************************************************************************************/

        function getConditionalColumn($table_name,$column_name, $condition_array)
                {
                $name=key($condition_array);
                $value=$condition_array[$name];
                $this->query="select $column_name as \"result\" from $table_name where $name='$value';";
                $this->runQuery();
                $column_name_array=$this->returnArrays();
                $column_name=$column_name_array[0]["result"];
                return $column_name;
                }
        }

/*****************************************************************************************
Function Name         :generateInsertQuery
No. of Arguments        :2
Arguments                :$table - database table where the values are to be inserted
                         $associative_array - associative array of values to be inserted
Action                        :generates the database query from the associative array with the table
                                and returns it.
Returns                        :database query as string.
Created By                :S.Gnanavel
Created On                :14 March 2003
Modified By                :R.Lakshmi Narayanan
Modified On                :27 May 2003
*****************************************************************************************/

function generateInsertQuery($table, $associative_array)
        {
	foreach ($associative_array as $key => $value)
                {
		$columnsarray[]=$key;
                // incase the value is string we always pass values in single quote
                $valuesarray[]=($value=="" && $value!="0")?"NULL":"'$value'";
                }
        // convert the column names array into a single string
        $columns= implode (',', $columnsarray);
        // convert the column values array in to a single string
        $values = implode (',', $valuesarray);
        // generate the query
        $query= "insert into $table ($columns) values ($values);";
        return $query;
        }

/*****************************************************************************************
Function Name         :createConditionString
No. of Arguments        :1
Arguments                :$conditions_array - array of conditions to be converted as
                                a string
Action                        :converts the given conditions_array as a string and returns it.
Returns                        :condition string
Created By                :S.Gnanavel
Created On                :14 March 2003
Modified By                :Ma.Sivakumar
Modified On                :26 May 2003
*****************************************************************************************/

function createConditionsString($conditions_array)
        {
        $query_string .=" where ";

        foreach ($conditions_array as $key => $value)
                {
                // check whether the array contains only one item or more than one items.
                if (count($conditions_array) ==1)   //if only one item end the query
                        $query_string .= " $key = '$value' ";
                else   //otherwise
                        {
                        $query_string .=" $key = '$value'"." and ";
                        //  and removes the first value in the array
                        array_shift($conditions_array);
                        }
                }
        return $query_string;
        }

/*****************************************************************************************
Function Name         :generateUpdateQuery
No. of Arguments        :3
Arguments                :$table - database table on which the values are to be updated
                         $values_array - associative array of values to be updated in the
                                 table
                         $conditions_array - associative array of conditions to be used
                                 for updating the values
Action                        :generates the database query for updation, as a string and returns it
Returns                        :database query as string
Created By                :S.Gnanavel
Created On                :14 March 2003
Modified By                :R.Lakshmi Narayanan
Modified On                :26 May 2003
*****************************************************************************************/

function generateUpdateQuery($table, $values_array, $conditions_array="")
        {
        $query="update $table set";
        // form the query string with the values to be updated
        foreach ($values_array as $key => $value)
                {
                $value=($value=="" && $value!="0")?"NULL":"'$value'";
                // check whether the array contains only one item or more than one items.
                if (count($values_array) ==1)   //if only one item end the query
                        $query .= " $key = $value ";
                        else   //otherwise
                                {
                                $query .=" $key = $value, ";
                                //  and removes the first value in the array
                                array_shift($values_array);
                                }
                }
        // check if conditions arrays is present then append with the query string
        if ($conditions_array)
                $query .= createConditionsString($conditions_array);
        $query.=";";
        return $query;
        }

/*****************************************************************************************
Function Name         :generatSelectQuery
No. of Arguments        :5
Arguments                :$table - database table used for selecting the values
                         $column_array - associative array of values to be selected
                         $condtitions_array - associative array of conditions to be applied
                         $ordering - order by column
                         $limit - offset for selecting the values.
Action                        :generates the database query for SELECT as a string and returns it
Returns                        :database query as string for select
Created By                :S.Gnanavel
Created On                :14 March 2003
Modified By                :M.Sivakumar
Modified On                :26 May 2003
*****************************************************************************************/

function generateSelectQuery($table, $column_array="", $conditions_array="", $ordering="",$limit="")
        {
        $query="select";
        // append the columns to be selected with the query string
        if ($column_array !="")
                {
                foreach ($column_array as $key => $value)
                        {
                        if (count($column_array) ==1)
                                $query .=" $key as \"$value\" ";
                        else
                                {
                                $query .=" $key as \"$value\" "." , ";
                                array_shift($column_array);
                                }
                        }
                }
        else
                $query .=" * ";
                // append the table name
        $query .=" from $table";

        // check if conditions present and if present append it with the query string
        if ($conditions_array)
                $query .= createConditionsString($conditions_array);

        // check ordering preference, if present append it with the query string
                if($ordering !="")
                        $query .=" order by $ordering";

                if($limit !="")
                        $query.=" LIMIT $limit";
        $query.=";";
        return $query;
        }

?>
